USE [CST]
GO
/****** Object:  StoredProcedure [dbo].[SP_001_RNV_Analysis]    Script Date: 11/10/2015 1:15:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SP_001_RNV_Analysis] @DocComp CHAR(5) = '00011', @AsOfDate DATE = '07-31-2015'
AS
BEGIN


-- *********************************************************************************************************************
-- *********************************************************************************************************************
-- JW
-- 2015-10-25
--
-- Parameter :
--		@DocComp - The DocumentCompany.  The default value will be '00011'
--		@AsOfDate - (MM-DD-YYYY) The cut off date for the RNV report. The default date wil be '07-31-2015'
-- Usage Example :
--		EXEC SP_001_RNV_Analysis '00040', '12-31-2014'
--
-- *********************************************************************************************************************
-- *********************************************************************************************************************

PRINT 'Showing results for DocumentCompany = ' + @DocComp + ' and AsOfDate (mm-dd-yyyy) = ' + CONVERT(VARCHAR,@AsOfDate,110) + CHAR(13);


-- #####################################################################################################################
-- #####################################################################################################################
-- SETUP : Temp table with analysis
-- #####################################################################################################################
-- #####################################################################################################################

SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tblRNV') IS NOT NULL DROP TABLE #tblRNV;
SELECT *
	,(CASE WHEN MAX_Voucher_GLDate IS NULL AND [RNV Amount (at as of date)] <> 0 THEN CONCAT('RNV as of ', @AsOfDate)
		   WHEN MAX_Voucher_GLDate IS NULL AND [RNV Amount (at as of date)] = 0 THEN CONCAT('OK (Zero Balance), RNV as of ', @AsOfDate)
		   WHEN MAX_Voucher_GLDate <= @AsOfDate AND [RNV Amount (at as of date)] <> 0 THEN CONCAT('RNV as of ', @AsOfDate, ', Partial Vouchered')
		   WHEN MAX_Voucher_GLDate <= @AsOfDate AND [RNV Amount (at as of date)] = 0 THEN 'OK'
		   ELSE '???' END) AS Notes
	,(CASE WHEN MAX_Receipt_GLDate >= '2014-01-01' THEN CONVERT(NVARCHAR(16), MAX_Receipt_GLDate)
		   ELSE	'Excluded' END) AS [Max_GLDate_2013 and Prior Excluded]
INTO #tblRNV
FROM (
	-- =======================================================================
	-- AA block
	-- Goods Received : DocumentType = 'OV'
	-- Pre-Filtered to @AsOfDate
	-- =======================================================================
	SELECT AA.DocumentCompany, AA.OrderNumber, AA.LineNumber, AA.NumberofLines
		,AA.AddressNumber, LTRIM(RTRIM(REPLACE(DD.AlphaName,CHAR(9),''))) AS [Vendor Name]
		,AA.DocumentType, AA.MAX_Receipt_GLDate, AA.SUM_AmountReceived
		,BB.DocumentType AS DocumentType2, BB.MAX_Voucher_GLDate, BB.SUM_AmountPaidtoDate
		,(AA.SUM_AmountReceived - ISNULL(BB.SUM_AmountPaidtoDate,0)) AS [RNV Amount (at as of date)]
		,(AA.SUM_AmountReceived - ISNULL(CC.SUM_AmountPaidtoDate_NOW,0)) AS [RNV Amount (Now)]
	FROM (
		SELECT DocumentCompany, OrderNumber, LineNumber, NumberofLines, AddressNumber, DocumentType
			,MAX(CONVERT(DATE, GLDate)) AS MAX_Receipt_GLDate
			,SUM(AmountReceived) AS SUM_AmountReceived
		FROM JDEPOPurchaseOrderReceiver
		WHERE DocumentCompany = @DocComp
		AND GLDate <= @AsOfDate
		AND DocumentType = 'OV'
		AND MatchRecordType = 1 -- Keep 1 (Receipt) / ignore 4 (Receipt Reversal)
		AND OrderTypeCode IN ('OO', 'OP') -- Standard PO (OP + OO) / ignore OT
		AND NOT (AmountPaidtoDate = 0 AND ReceivedQuantity = QuantityReturned)
		GROUP BY DocumentCompany, OrderNumber, LineNumber, NumberofLines, AddressNumber, DocumentType
	) AA
	-- =======================================================================
	-- BB block
	-- Vouchered : DocumentType = 'P2' or 'P3'
	-- Pre-Filtered to @AsOfDate
	-- Used to match AA block for RNV on @AsOfDate
	-- =======================================================================
	LEFT JOIN (
		SELECT DocumentCompany, OrderNumber, LineNumber, NumberofLines, AddressNumber, DocumentType
			,MAX(CONVERT(DATE, GLDate)) AS MAX_Voucher_GLDate
			,SUM(AmountPaidtoDate) AS SUM_AmountPaidtoDate
		FROM JDEPOPurchaseOrderReceiver
		WHERE DocumentCompany = @DocComp
		AND GLDate <= @AsOfDate
		AND NumberofLines <> 0
		AND DocumentType IN ('P2','P3')
		AND MatchRecordType = 2 -- Keep 2 (Voucher) / ignore 3 (Voucher Reversal)
		AND OrderTypeCode IN ('OO', 'OP') -- Standard PO (OP + OO) / ignore OT
		GROUP BY DocumentCompany, OrderNumber, LineNumber, NumberofLines, AddressNumber, DocumentType
	) BB
		ON AA.DocumentCompany = BB.DocumentCompany
		AND AA.OrderNumber = BB.OrderNumber
		AND AA.LineNumber = BB.LineNumber
		AND AA.NumberofLines = BB.NumberofLines
		AND AA.AddressNumber = BB.AddressNumber
	-- =======================================================================
	-- CC block
	-- Same as BB block, but do NOT filtered on @AsOfDate
	-- Used to match AA block for RNV for current date (not @AsOfDate)
	-- =======================================================================
	LEFT JOIN (
		SELECT DocumentCompany, OrderNumber, LineNumber, NumberofLines, AddressNumber, DocumentType
			,MAX(CONVERT(DATE, GLDate)) AS MAX_Voucher_GLDate_NOW
			,SUM(AmountPaidtoDate) AS SUM_AmountPaidtoDate_NOW
		FROM JDEPOPurchaseOrderReceiver
		WHERE DocumentCompany = @DocComp
		--AND GLDate <= @AsOfDate -- Do not filter by @AsOfDate
		AND NumberofLines <> 0
		AND DocumentType IN ('P2','P3')
		AND MatchRecordType = 2 -- Keep 2 (Voucher) / ignore 3 (Voucher Reversal)
		AND OrderTypeCode IN ('OO', 'OP') -- Standard PO (OP + OO) / ignore OT
		GROUP BY DocumentCompany, OrderNumber, LineNumber, NumberofLines, AddressNumber, DocumentType
	) CC
		ON AA.DocumentCompany = CC.DocumentCompany
		AND AA.OrderNumber = CC.OrderNumber
		AND AA.LineNumber = CC.LineNumber
		AND AA.NumberofLines = CC.NumberofLines
		AND AA.AddressNumber = CC.AddressNumber
		AND BB.DocumentType = CC.DocumentType
	-- =======================================================================
	-- DD block
	-- Just get vendor name
	-- =======================================================================
	LEFT JOIN JDEABAddressBookMaster DD
		ON AA.AddressNumber = DD.AddressNumber
) EE
WHERE LEFT((CASE WHEN MAX_Voucher_GLDate IS NULL AND [RNV Amount (at as of date)] <> 0 THEN CONCAT('RNV as of ', @AsOfDate)
		   WHEN MAX_Voucher_GLDate IS NULL AND [RNV Amount (at as of date)] = 0 THEN CONCAT('OK (Zero Balance), RNV as of ', @AsOfDate)
		   WHEN MAX_Voucher_GLDate <= @AsOfDate AND [RNV Amount (at as of date)] <> 0 THEN CONCAT('RNV as of ', @AsOfDate, ', Partial Vouchered')
		   WHEN MAX_Voucher_GLDate <= @AsOfDate AND [RNV Amount (at as of date)] = 0 THEN 'OK'
		   ELSE '???' END),2) <> 'OK'
ORDER BY DocumentCompany, OrderNumber, LineNumber, NumberofLines, AddressNumber;
SET NOCOUNT OFF;


-- #####################################################################################################################
-- #####################################################################################################################
-- RESULT : By Vendor
-- #####################################################################################################################
-- #####################################################################################################################

PRINT '----------------------------------------------------------------------------------';
PRINT 'Summary by Vendor';
SELECT [Vendor Name]
	,SUM(ISNULL([RNV Amount (at as of date)],0)) AS [RNV Amount (at as of date)]
	,SUM(ISNULL([RNV Amount (Now)],0)) AS [RNV Amount (Now)]
	,SUM(CASE WHEN [Max_GLDate_2013 and Prior Excluded] <> 'Excluded'
			  THEN ISNULL([RNV Amount (at as of date)],0)
			  ELSE 0 END ) AS [RNV Amount (if Excluded at as of date)]
	,SUM(CASE WHEN [Max_GLDate_2013 and Prior Excluded] <> 'Excluded'
			  THEN ISNULL([RNV Amount (Now)],0)
			  ELSE 0 END) AS [RNV Amount (if Excluded Now)]
FROM #tblRNV
GROUP BY [Vendor Name]
ORDER BY [Vendor Name];



-- #####################################################################################################################
-- #####################################################################################################################
-- RESULT : Details
-- #####################################################################################################################
-- #####################################################################################################################

PRINT '----------------------------------------------------------------------------------';
PRINT 'Analysis Detail';
SELECT *
FROM #tblRNV
ORDER BY DocumentCompany, OrderNumber, LineNumber, NumberofLines, AddressNumber;










END
